/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.twistinjava.userDAO;

import java.util.ArrayList;
import com.twistinjava.exception.TwistinjavaException;
import com.twistinjava.logon.User;
import com.twistinjava.util.DBManager;
import com.twistinjava.util.LoggableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.apache.commons.logging.*;

/**
 *
 * @author shikhar
 */
public class UserDAO
{

    private static Log log = LogFactory.getLog(UserDAO.class);

    public ArrayList getAllUser() throws TwistinjavaException
    {
        log.debug("Inside getAllUser() method of UserDAO Class ****************");
        String sqlQry = "";
        Connection conn = null;
        PreparedStatement pStmt = null;
        ArrayList users = new ArrayList();
        try
        {
            conn = DBManager.getConnection();
            sqlQry = "select id, logonID, firstName, lastName, emailID from User";
            pStmt = new LoggableStatement(conn, sqlQry.toString());
            ;
            ResultSet rs = pStmt.executeQuery();
            while (rs.next())
            {
                User user = new User();
                user.setId(rs.getInt("id"));
                user.setLogonID(rs.getString("logonID"));
                user.setFirstName(rs.getString("firstName"));
                user.setLastName(rs.getString("lastname"));
                user.setEmail(rs.getString("emailID"));
                users.add(user);

            }
        }
        catch (SQLException sQLException)
        {
            log.error("Exception inside getAllUser() method of AdminDAO class ********" + sQLException.getMessage());
            throw new TwistinjavaException(sQLException.getMessage());
        }
        finally
        {
            try
            {
                if (pStmt != null)
                {
                    pStmt.close();
                }
                if (conn != null)
                {
                    conn.close();
                }
            }
            catch (SQLException sqle)
            {
                log.error("Inside AdminDAO :getAllUser() method of AdminDAO class :" + sqle.getMessage());
            }

        }
        return users;
    }

    public User getUser(int id) throws TwistinjavaException
    {
        log.debug("Inside getUser(int id) method of UserDAO Class ****************");
        String sqlQry = "";
        Connection conn = null;
        PreparedStatement pStmt = null;
        User user = null;
        try
        {
            conn = DBManager.getConnection();
            sqlQry = "select id, logonID, firstName, lastName, emailID, password from User where id = ?";
            pStmt = new LoggableStatement(conn, sqlQry.toString());
            pStmt.setInt(1, id);
            ResultSet rs = pStmt.executeQuery();
            if (rs.next())
            {
                user = new User();
                user.setId(rs.getInt("id"));
                user.setLogonID(rs.getString("logonID"));
                user.setFirstName(rs.getString("firstName"));
                user.setLastName(rs.getString("lastname"));
                user.setEmail(rs.getString("emailID"));
                user.setPassword(rs.getString("password"));

            }
        }
        catch (SQLException sQLException)
        {
            log.error("Exception inside getUser(int id) method of AdminDAO class ********" + sQLException.getMessage());
            throw new TwistinjavaException(sQLException.getMessage());
        }
        finally
        {
            try
            {
                if (pStmt != null)
                {
                    pStmt.close();
                }
                if (conn != null)
                {
                    conn.close();
                }
            }
            catch (SQLException sqle)
            {
                log.error("Inside AdminDAO :getUser(int id) method of AdminDAO class :" + sqle.getMessage());
            }

        }
        return user;
    }

    public void deleteUser(int id) throws TwistinjavaException
    {
        log.debug("Inside deleteUser(int id) method of UserDAO Class ****************");
        String sqlQry = "";
        Connection conn = null;
        PreparedStatement pStmt = null;
        try
        {
            conn = DBManager.getConnection();
            sqlQry = "delete from User where id = ?";
            pStmt = new LoggableStatement(conn, sqlQry.toString());
            pStmt.setInt(1, id);
            pStmt.execute();

        }
        catch (SQLException sQLException)
        {
            log.error("Exception inside deleteUser(int id) method of AdminDAO class ********" + sQLException.getMessage());
            throw new TwistinjavaException(sQLException.getMessage());
        }
        finally
        {
            try
            {
                if (pStmt != null)
                {
                    pStmt.close();
                }
                if (conn != null)
                {
                    conn.close();
                }
            }
            catch (SQLException sqle)
            {
                log.error("Inside AdminDAO :deleteUser(int id) method of AdminDAO class :" + sqle.getMessage());
            }

        }

    }

    public void updateUser(User user) throws TwistinjavaException
    {
        log.debug("updateUser(User user) method of UserDAO Class ****************");
        String sqlQry = "";
        Connection conn = null;
        PreparedStatement pStmt = null;

        try
        {
            conn = DBManager.getConnection();
            sqlQry = "Update user set logonID = ?, firstName = ?, lastName = ?, emailID = ?, password= ? where id = ?";
            pStmt = new LoggableStatement(conn, sqlQry.toString());
            pStmt.setString(1, user.getLogonID());
            pStmt.setString(2, user.getFirstName());
            pStmt.setString(3, user.getLastName());
            pStmt.setString(4, user.getEmail());
            pStmt.setString(5, user.getPassword());
            pStmt.setInt(6, user.getId());

            pStmt.executeUpdate();

        }
        catch (SQLException sQLException)
        {
            log.error("Exception inside updateUser(User user) method of AdminDAO class ********" + sQLException.getMessage());
            throw new TwistinjavaException(sQLException.getMessage());
        }
        finally
        {
            try
            {
                if (pStmt != null)
                {
                    pStmt.close();
                }
                if (conn != null)
                {
                    conn.close();
                }
            }
            catch (SQLException sqle)
            {
                log.error("Inside AdminDAO :updateUser(User user) method of AdminDAO class :" + sqle.getMessage());
            }

        }
        
    }

}
